package com.sg.java.util;

import org.apache.commons.lang3.tuple.Pair;
import org.apache.commons.lang3.tuple.Triple;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.StringJoiner;
import java.util.function.Function;

public class SqlUtils implements Serializable {

    @SuppressWarnings("unchecked")
    @SafeVarargs
    public static <T> String newInsertSql(String tableName, List<T> records, Triple<String, Function<T, Object>, Boolean>... cols_gets_withQuotationMarks) {
        String[]                             cols                    = new String[cols_gets_withQuotationMarks.length];
        Pair<Function<T, Object>, Boolean>[] gets_withQuotationMarks = new Pair[cols_gets_withQuotationMarks.length];
        for (int i = 0; i < cols_gets_withQuotationMarks.length; i++) {
            cols[i]                    = cols_gets_withQuotationMarks[i].getLeft();
            gets_withQuotationMarks[i] = Pair.of(cols_gets_withQuotationMarks[i].getMiddle(), cols_gets_withQuotationMarks[i].getRight());
        }
        return preInsert(tableName, cols) + postInsert(records, gets_withQuotationMarks);
    }

    public static String preInsert(String tableName, String... columnNames) {
        final StringJoiner sj = new StringJoiner(",");
        for (String columnName : columnNames) {
            sj.add(columnName);
        }
        return "insert into " + tableName + " (" + sj + ") values\n";
    }

    @SafeVarargs
    public static <T> String postInsert(List<T> records, Pair<Function<T, Object>, Boolean>... gets_withQuotationMarks) {
        StringJoiner valuesSql = new StringJoiner(",\n");
        for (T t : records) {
            StringJoiner valueSql = new StringJoiner(",", "(", ")");
            for (Pair<Function<T, Object>, Boolean> pair : gets_withQuotationMarks) {
                valueSql.add(toSqlValue(pair.getLeft().apply(t), pair.getRight()));
            }
            valuesSql.add(valueSql.toString());
        }
        return valuesSql.toString();
    }

    public static String toSqlValue(Object o, Boolean withQuotationMarks) {
        if (o != null && !o.equals("null")) {
            String v = o.toString();
            if (o instanceof String) {
                if (withQuotationMarks == null || withQuotationMarks) {
                    if (v.contains("'")) {
                        //pg单引号转义
                        v = v.replaceAll("'", "''");
                    }
                    v = "'" + v + "'";
                }
            }
            else if (o instanceof Double) {
                if (Double.isNaN((Double) o)) {
                    v = "null";
                }
                if (withQuotationMarks != null && withQuotationMarks) {
                    v = "'" + v + "'";
                }
            }
            else if (o instanceof Float) {
                if (Float.isNaN((Float) o)) {
                    v = "null";
                }
                if (withQuotationMarks != null && withQuotationMarks) {
                    v = "'" + v + "'";
                }
            }
            else if (o instanceof Date) {
                if (o instanceof Timestamp) {
                    v = "'" + DateUtils.to_yyyy_MM_dd_HH_mm_ss_Str((Timestamp) o) + "'";
                }
                else if (o instanceof Time) {
                    v = "'" + DateUtils.to_HH_mm_ss_Str((Time) o) + "'";
                }
                else {
                    v = "'" + DateUtils.to_yyyy_MM_dd_Str((Date) o) + "'";
                }
            }
            else {
                if (withQuotationMarks != null && withQuotationMarks) {
                    v = "'" + v + "'";
                }
            }
            return v;
        }
        else {
            return null;
        }
    }

    //    public static Properties cqPgProp = PropertiesUtil.createPropertiesFromResource(ResourcePath.dws_business_jdbc_properties);
    public static Properties cqPgProp = new Properties();

    public static Properties homePgProp = new Properties();

    static {
//        homePgProp.setProperty("url", "jdbc:postgresql://piscn.vicp.cc:55432/volt?");
//        homePgProp.setProperty("user", "volt");
//        homePgProp.setProperty("password", "volt2022");
//        homePgProp.setProperty("url", "jdbc:postgresql://192.168.1.165:5432/assetmanagement?");
        homePgProp.setProperty("url", "jdbc:postgresql://192.168.1.165:5432/test");
        homePgProp.setProperty("user", "postgres");
        homePgProp.setProperty("password", "postgres");
        //测试
//        cqPgProp.setProperty("url", "jdbc:postgresql://25.64.68.232:25432/assetmanagement?");
//        cqPgProp.setProperty("user", "pms3_qkjdy");
//        cqPgProp.setProperty("password", "pms3_qkjdy");
        //正式
        cqPgProp.setProperty("url", "jdbc:postgresql://10.185.12.245:25432/assetmanagement?");
        cqPgProp.setProperty("user", "pms3_qkjdy");
        cqPgProp.setProperty("password", "pms3_qkjdy");
    }

    static {
        try {
//            Class.forName("com.huawei.gauss200.jdbc.Driver");
            Class.forName("org.postgresql.Driver");
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection newPgConn(Properties pgProp) throws Exception {
        String url      = pgProp.getProperty("url");
        String user     = pgProp.getProperty("user");
        String password = pgProp.getProperty("password");
        return DriverManager.getConnection(url, user, password);
    }

    public static Connection newCqPgConn() throws Exception {
//        String url      = "jdbc:postgresql://25.64.68.232:25432/assetmanagement?";
        String url      = "jdbc:postgresql://10.185.12.245:25432/assetmanagement?";
        String user     = "pms3_qkjdy";
        String password = "pms3_qkjdy";
        return DriverManager.getConnection(url, user, password);
    }

}
